get-the-solution

MySQL Cursor alternative – Dynamische SQL Cursor

By
on Regards: DBMS;

vor kurzem stand ich vor dem Problem, dass ich in einer MySQL Stored Procedure einen Cursor benötigt habe. Das Problem an der ganzen Sache war, dass für die Cursor Deklatraion einige Abfragen (u.a insert into, select usw.) vorausgesetzt wurden. Das Cursor-Statment wird quais während der Laufzeit “generiert”.

Das hat zur Folge, dass man einen Deklaration Block hat auf den eben die nötigen Abfragen zum Zusammenstellen des Cursor-Statments machen muss. Erst dann kann der Cursor mit DECLARE definiert werden. Das Problem wie folgt:

CREATE PROCEDURE `tsc_sp_add_page` (in idfolder int(11), in title varchar(65), in template varchar(100))
COMMENT 'creates a new page and adds all wildcards which will be shown infolder,InLanguage,InAll,InPage'
BEGIN

--id of the inserted page

DECLARE pageid INT(11);

//...ein paar Deklaration

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

//...dann die vorausgesetzten Abfragen für die Definition des Cursors

--get the language of the page
set pagelangid = (SELECT f.idlanguage as pagelangid FROM tsc_folder AS f WHERE f.id=idfolder LIMIT 0 , 1);

--first we create the page record
INSERT INTO `tsc_page` (`id` ,`idfolder` , `title` , `template`) VALUES (NULL , idfolder, title , template);
set pageid = LAST_INSERT_ID();

//...cursor deklaration ist abhänig vom insert into statment
declare c_wildcardInAll cursor FOR select distinct w.id from tsc_wildcard AS w, tsc_page_have_wildcard AS wh, tsc_page AS p, tsc_folder AS f
WHERE wh.idpage = p.id
AND wh.idwildcard = w.id
AND p.idfolder = f.id
AND w.InLanguage =1
AND f.idlanguage =pagelangid;

Laut MySQL Syntax müssen aber alle Deklaration ganz zu Beginn der SP stehen. Nach der ersten Abfrage darf keine weitere Deklaration mehr folgen. Mit dem obigen Code-Ausschnitt erhält man dann die Meldung:

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare c_wildcardInAll cursor FOR select distinct w.id from tsc_wildcard AS w, ' at line 29 Line 7, column 1

Jetzt wäre es toll, wenn es so etwas wie dynamische Cursor oder Parameter Cursor geben würde, was aber in MySQL 5.0.24 nicht der Fall war (Evtl wird ein solches Feature bei der nächsten Version nachgereicht?, Orcael kennt z.B. Cursor mit Parameter).

Um keine Cursor verwenden zu müssen, kann man eine temporäre Tabelle verwenden, die quasi als Cursor fungiert. Die temporäre Tabelle muss genau auf die Abfrage angepasst werden. Zusätzlich benötigt man eine weitere RowID Spalte um in der Schleife auf alle Datensätze zugreifen zu können, bzw. um iterieren zu können.

1.) Temporäre Tabelle erstellen

Da ich bei meiner Abfrage nur eine Spalte abfrage, sieht meine temporäre Tabelle recht simpel aus.

CREATE TEMPORARY TABLE twildcardInLang (
`RowID` int(11) NOT NULL auto_increment PRIMARY KEY,
`id` int NOT NULL
) ENGINE=MEMORY;

2.) Daten abfragen und in die Tabelle schreiben

Wichtig ist, dass die RowID die Eigenschaften auto_increment und PRIMARY KEY hat. Ansonsten kann man nicht durch die Datensätze iterieren. Danach fügen wir die Datensätze in die temporäre Tabelle ein.

INSERT INTO twildcardInLang (id) select distinct w.id from tsc_wildcard AS w, tsc_page_have_wildcard AS wh, tsc_page AS p, tsc_folder AS f
WHERE wh.idpage = p.id
AND wh.idwildcard = w.id
AND p.idfolder = f.id
AND w.InLanguage =1
AND f.idlanguage =pagelangid;

3.)Hilfsvariable für die Schleife setzen

Nun müssen wir wissen wie oft wir die Schleife durchlaufen wollen. Dies entspricht logischerweise der Anzahl der Datensätze in unserer temporären Tabelle. Als nächstes setzen wir die Zähl-Variable auf 1 um die Schleife durchlaufen- und die Daten der Tabelle abfragen zu können.

SET @NumberRecords = (select count(id) as id from twildcardInLang);
SET @RowCount = 1;

4.) Die Schleife erstellen

Der Code der Schleife sollte nun für sich sprechen.

WHILE @RowCount <= @NumberRecords DO
set @wildcardid = (SELECT id FROM twildcardInLang WHERE RowID = @RowCount);

...irgendwas mit @wildcardid machen

SET @RowCount := (@RowCount + 1);
END WHILE;

5.) Am Schluss entfernen wir die temporäre Tabelle mit DROP TABLE twildcardInLang;

Der komplette Code-Ausschnitt sieht dann in etwa so aus:

CREATE TEMPORARY TABLE twildcardInLang (
`RowID` int(11) NOT NULL auto_increment PRIMARY KEY,
`id` int NOT NULL
) ENGINE=MEMORY;

--get InLanguage=pagelangid wildcards with proper language wildcards and insert it into our temporary table (which we use as "virtual curosr")

INSERT INTO twildcardInLang (id) select distinct w.id from tsc_wildcard AS w, tsc_page_have_wildcard AS wh, tsc_page AS p, tsc_folder AS f

WHERE wh.idpage = p.id
AND wh.idwildcard = w.id
AND p.idfolder = f.id
AND w.InLanguage =1
AND f.idlanguage =pagelangid;
-- Get the number of records in the temporary table
SET @NumberRecords = (select count(id) as id from twildcardInLang);
SET @RowCount = 1;
WHILE @RowCount <= @NumberRecords DO
set @wildcardid = (SELECT id FROM twildcardInLang WHERE RowID = @RowCount);
-- link wildcard with page
INSERT INTO `tsc_page_have_wildcard` (`idpage`, `idwildcard`) VALUES (pageid, @wildcardid);
SET @RowCount := (@RowCount + 1);
END WHILE;
DROP TABLE twildcardInLang;

Links zum Thema: